package com.thornbird.vehiclerecycling.repository;

import com.thornbird.vehiclerecycling.entity.Order;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

public interface OrderRepository extends JpaRepository<Order, Long> {

    String CONDITION = "(serial_number ilike :keyword or certificate_number ilike :keyword " +
            "or company_name ilike :keyword or owner_name ilike :keyword or id_number ilike :keyword " +
            "or phone_number ilike :keyword or vehicle_number ilike :keyword or vehicle_type ilike :keyword " +
            "or vehicle_nature ilike :keyword or vehicle_brand ilike :keyword or vehicle_model ilike :keyword " +
            "or vin ilike :keyword) and ((delivery_date >= :startDate and delivery_date <= :endDate) " +
            "or (registration_date >= :startDate and registration_date <= :endDate))";

    String FIND_SQL = "select * from orders where " + CONDITION + " order by created_at desc";

    String COUNT_SQL = "select count(1) from orders where " + CONDITION;

    @Query(nativeQuery = true, value = FIND_SQL, countQuery = COUNT_SQL)
    Page<Order> findByCondition(String keyword, Date startDate, Date endDate, Pageable pageable);

    Order findFirstById(Long id);

    Order findFirstBySerialNumberAndDeletedIsFalse(String serialNumber);

    Order findFirstByCertificateNumberAndDeletedIsFalse(String certificateNumber);

    @Query(nativeQuery = true, value = "select company_name from orders where company_name is not null "
            + "and company_name != '' group by company_name order by company_name")
    List<String> findCompanyNames();

    @Query(nativeQuery = true, value = "select vehicle_brand from orders where vehicle_brand is not null "
            + "and vehicle_brand != '' group by vehicle_brand order by vehicle_brand")
    List<String> findVehicleBrands();

    @Query(nativeQuery = true, value = "select vehicle_model from orders where vehicle_model is not null "
            + "and vehicle_model != '' group by vehicle_model order by vehicle_model")
    List<String> findVehicleModels();

    @Query(nativeQuery = true, value = "select vehicle_management_office from orders where "
            + "vehicle_management_office is not null and vehicle_management_office != '' "
            + "group by vehicle_management_office order by vehicle_management_office")
    List<String> findVehicleManagementOffices();

    @Query(nativeQuery = true, value = "select recovered_materials from orders where "
            + "recovered_materials is not null and recovered_materials != '' "
            + "group by recovered_materials order by recovered_materials")
    List<String> findRecoveredMaterials();

    @Query(nativeQuery = true, value = "select unrevoked_materials from orders where "
            + "unrevoked_materials is not null and unrevoked_materials != '' "
            + "group by unrevoked_materials order by unrevoked_materials")
    List<String> findUnrevokedMaterials();

    @Modifying
    void deleteById(Long id);

}
